[T4Scaffolding.Scaffolder(Description = "Allows you to modify the T4 template rendered by a scaffolder")][CmdletBinding()]
param(     
	[string]$BaseEntityType,
    [string]$Project,
    [string]$CodeLanguage,
	[string]$Server = ".",
	[parameter(Mandatory = $true)][string]$Database,
	[string[]]$IncludedTables,
	[string[]]$ExcludedTables,
	[string[]]$TemplateFolders,
	[switch]$Singularize = $false,
	[switch]$Force = $false
)

$defaultNamespace = (Get-Project $Project).Properties.Item("DefaultNamespace").Value
$tables = New-Object System.Collections.Generic.List``1[System.String]


$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "server=" + $Server + ";database=" + $Database + ";trusted_connection=yes;"
$connection.Open()

# Get tables with a single primary key column
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "
	SELECT 
		T.TABLE_NAME
	FROM
		INFORMATION_SCHEMA.TABLES T
	WHERE
		T.TABLE_TYPE = 'BASE TABLE' 
		AND 
		--T.TABLE_NAME = 'Patient' 
		--AND
		T.TABLE_NAME NOT LIKE 'aspnet_%'
		AND
		T.TABLE_NAME NOT LIKE 'sys%'
		AND
		(
			SELECT
				COUNT(*)
			FROM
				INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
					INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.TABLE_NAME = CCU.TABLE_NAME AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
			WHERE
				TC.TABLE_NAME = T.TABLE_NAME
				AND
				TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
		) = 1"

if ($IncludedTables)
{
	$command.CommandText += "
		AND
		T.TABLE_NAME IN ("
		
		foreach ($includedTable in $IncludedTables)
		{
			$command.CommandText += "'" + $includedTable + "',"
		}
		
		$command.CommandText += "'__Dummy__')"
}

if ($ExcludedTables)
{
	$command.CommandText += "
		AND
		T.TABLE_NAME NOT IN ("
		
		foreach ($excludedTable in $ExcludedTables)
		{
			$command.CommandText += "'" + $excludedTable + "',"
		}
		
		$command.CommandText += "'__Dummy__')"
}

$command.CommandText += "
	ORDER BY 
		T.TABLE_NAME"

$command.Connection = $connection

$reader = $command.ExecuteReader()

while ($reader.Read())
{
	$tables.Add($reader.GetString($reader.GetOrdinal("TABLE_NAME")));
}

$reader.Close()

foreach ($table in $tables)
{
	Write-Host $table
	$properties = New-Object System.Collections.Generic.List``1[System.Object]

	$command = New-Object System.Data.SqlClient.SqlCommand
	$command.CommandText = "
		SELECT 
			C.COLUMN_NAME, 
			DATA_TYPE, 
			IS_NULLABLE, 
			CHARACTER_MAXIMUM_LENGTH,
			(
				SELECT
					COUNT(*)
				FROM
					INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
						INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.TABLE_NAME = CCU.TABLE_NAME AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
				WHERE
					TC.TABLE_NAME = C.TABLE_NAME
					AND
					TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
					AND
					CCU.COLUMN_NAME = C.COLUMN_NAME
			)
			[IS_PRIMARY_KEY],
			(
				SELECT
					COUNT(*)
				FROM
					INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
						INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.TABLE_NAME = CCU.TABLE_NAME AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
				WHERE
					TC.TABLE_NAME = C.TABLE_NAME
					AND
					TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
					AND
					CCU.COLUMN_NAME = C.COLUMN_NAME
			)
			[IS_FOREIGN_KEY],
			(
				SELECT 
					KCU2.TABLE_NAME
				FROM 
					INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
						INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON 
							KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 
							AND 
							KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
							AND 
							KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
						INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON 
							KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG 
							AND 
							KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
							AND 
							KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
				WHERE 
					KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
					AND
					KCU1.TABLE_NAME = C.TABLE_NAME
					AND
					KCU1.COLUMN_NAME = C.COLUMN_NAME
			)
			[FOREIGN_KEY_TABLE]
		FROM 
			INFORMATION_SCHEMA.COLUMNS C
		WHERE 
			TABLE_NAME = '" + $table + "'
		ORDER BY
			ORDINAL_POSITION,
			C.COLUMN_NAME"

	$command.Connection = $connection
	
	$reader = $command.ExecuteReader()

	while ($reader.Read())
	{
		$attributes = New-Object System.Collections.Hashtable
		
		for ($i = 0; $i -lt $reader.FieldCount; $i++)
		{
			$attributes.Add($reader.GetName($i), $reader.GetValue($i))
		}

		$properties.Add($attributes)
	}

	$reader.Close()

	$name = $table

	if ($Singularize)
	{
		$name = Get-SingularizedWord $table
	}
	
	$templateFile = Find-ScaffolderTemplate Entity -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
	$outputPath = $name

	$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
		Name = $name;
		Table = $table;
		DefaultNamespace = $defaultNamespace; 
		Properties = $properties;
	} -Project $Project -OutputPath $name -Force:$Force -Debug -Verbose

	$templateFile = Find-ScaffolderTemplate EntityOverride -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
	$outputPath = $name + "Override"
	$outputPath = Join-Path Alterations $outputPath
	$outputPath = Join-Path Mapping $outputPath

	$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
		Name = $name;
		Table = $table;
		DefaultNamespace = $defaultNamespace;
		Properties = $properties;
	} -Project $Project -OutputPath $outputPath -Force:$Force

	$templateFile = Find-ScaffolderTemplate EntityOverride.Custom -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
	$outputPath = $name + "Override.Custom"
	$outputPath = Join-Path Alterations $outputPath
	$outputPath = Join-Path Mapping $outputPath

	#$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	#	Name = $name;
	#	Table = $table;
	#	DefaultNamespace = $defaultNamespace;
	#	Properties = $properties;
	#} -Project $Project -OutputPath $outputPath
}

$connection.Close()

$templateFile = Find-ScaffolderTemplate App -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage "config" -ErrorIfNotFound
$outputPath = "App"

$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	Server = $Server;
	Database = $Database;
} -Project $Project -OutputPath $outputPath -Force:$Force

$templateFile = Find-ScaffolderTemplate FluentConfigurationExtensions -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
$outputPath = "FluentConfigurationExtensions"
$outputPath = Join-Path Mapping $outputPath

$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	DefaultNamespace = $defaultNamespace;
	Database = $Database;
	Entity = $tables[0];
} -Project $Project -OutputPath $outputPath -Force:$Force

$templateFile = Find-ScaffolderTemplate ContainerExtensions -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
$outputPath = "ContainerExtensions"
$outputPath = Join-Path Configuration $outputPath

$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	DefaultNamespace = $defaultNamespace;
	Database = $Database;
} -Project $Project -OutputPath $outputPath -Force:$Force

$templateFile = Find-ScaffolderTemplate EntityTest -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
$outputPath = "EntityTest"
$outputPath = Join-Path Tests $outputPath

$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	DefaultNamespace = $defaultNamespace;
	Database = $Database;
	Entity = $tables[0];
} -Project $Project -OutputPath $outputPath -Force:$Force

$templateFile = Find-ScaffolderTemplate SmokeTest -TemplateFolders $TemplateFolders -Project $Project -CodeLanguage $CodeLanguage -ErrorIfNotFound
$outputPath = "SmokeTest"
$outputPath = Join-Path Tests $outputPath

$wroteFile = Invoke-ScaffoldTemplate -Template $templateFile -Model @{ 
	DefaultNamespace = $defaultNamespace;
	Database = $Database;
	Entity = $tables[0];
} -Project $Project -OutputPath $outputPath -Force:$Force

